package com.eascs.web.o2o.web.bi.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.bi.dao.BIDao;
import com.eascs.web.o2o.web.bi.entity.BIOverdue;
import com.eascs.web.o2o.web.bi.entity.BIOverdueDetail;
import com.eascs.web.o2o.web.bi.vo.LoanDetailForm;

/**
 * 逾期信息
 * 
 * @author ycz
 *
 */
@Service
public class OverdueService {
	@Autowired
	private BIDao biDao;

	public List<BIOverdue> queryOverdue() {
		String sql = "SELECT T.REGIONAL_NAME  AS provincial,SUM(H.DEFAULTAMOUNT) AS overdueBalance " + " FROM O2O_PLATFORM_REGIONAL T " + " LEFT JOIN (SELECT Y.LTD,O.DEFAULTAMOUNT FROM O2O_OVERDUE_REPORT O " + " LEFT JOIN O2O_LOANAPPLY Y ON O.BILLCODE = Y.BILL_CODE WHERE O.STATUS IN('1','2')) "
				+ " H ON T.PLATFORM_ID = H.LTD GROUP BY T.REGIONAL_NAME";
		return biDao.findEntitys(sql, BIOverdue.class);
	}

	/**
	 * 逾期明细
	 * 
	 * @param LoanDetailForm
	 * @return
	 */
	public List<BIOverdueDetail> findOverdueDetailByPage(LoanDetailForm loanDetailForm) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T.loanapplyno as physicalId, T.BILL_CODE as billCode, T.loan_id as customerId,T.LOAN_NAME as customerName, T.province_name as provincial, T.OPERATOR_AGENCY as ltd, T.BANKREFUND as loanBank, T.`STATUS` as overdueType, T.DEFAULT_AMOUT as overdueAmount, T.DISPOSAL_DATE as overdueDate FROM ( SELECT LBI.BILL_CODE, LBI.loan_id, BIO.loanapplyno AS loanapplyno, opr.province_name AS province_name, CONCAT( LBI.ltd_code, '-', LBI.ltd_name ) AS OPERATOR_AGENCY, BIO.ltd AS ltd, CONCAT( LEFT (os.user_full_name, 1), '**' ) AS LOAN_NAME, o.item_name AS BANKREFUND, IFNULL(BIO.defaultamount, 0) AS DEFAULT_AMOUT, ( SELECT o.item_name FROM pub_dditem o WHERE dd_no = '11460' AND o.item_code = BIO.`STATUS` ) AS `STATUS`, BIO.disposaldate AS DISPOSAL_DATE FROM o2o_overdue_report AS BIO INNER JOIN o2o_loanapply AS LBI ON LBI.BILL_CODE = BIO.BILLCODE LEFT JOIN o2o_loan_user os ON os.loan_id = LBI.loan_id AND LBI.loanapply_no = os.loanapply_no AND os.spouse_id IS NULL AND os.sponsor_type IS NULL LEFT JOIN o2o_platform_regional opr ON opr.platform_id = BIO.ltd LEFT JOIN ( SELECT o.item_name, o.item_code FROM pub_dditem o WHERE o.dd_no = 10340 GROUP BY o.item_code ) o ON LBI.bank_loan_name = o.item_code WHERE LBI.ISLOCK = 'N' AND BIO.ISNEW = 'Y' ORDER BY BIO.disposaldate DESC ) AS T WHERE 1 = 1");

		if (StringUtil.hasText(loanDetailForm.getStartDate())) {
			sql.append(" and T.DISPOSAL_DATE >= ");
			sql.append("'");
			sql.append(loanDetailForm.getStartDate());
			sql.append("'");
		}
		if (StringUtil.hasText(loanDetailForm.getEndDate())) {
			sql.append(" and T.DISPOSAL_DATE <  ");
			sql.append("'");
			sql.append(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(loanDetailForm.getEndDate()), 1)));
			sql.append("'");
		}

		sql.append(" order by T.DISPOSAL_DATE desc ");
		return biDao.findEntitys(sql.toString(),BIOverdueDetail.class);
	}
}
